{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c28f67e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy\n",
    "import pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35551194",
   "metadata": {},
   "source": [
    "# pandas加载数据"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "64fc7ef1",
   "metadata": {},
   "source": [
    "- csv数据\n",
    "- Excel数据\n",
    "- MySQL数据库数据"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "86b32091",
   "metadata": {},
   "source": [
    "- to_csv()\n",
    "- read_csv()\n",
    "---\n",
    "- to_excel()\n",
    "- read_excel()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "23ee0a4d",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ab30cec2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple\n",
      "Requirement already satisfied: pymysql in d:\\software\\anaconda3\\lib\\site-packages (1.1.0)\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "pip install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "079009bb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simplw\n",
      "Requirement already satisfied: sqlalchemy in d:\\software\\anaconda3\\lib\\site-packages (1.4.39)\n",
      "Requirement already satisfied: greenlet!=0.4.17 in d:\\software\\anaconda3\\lib\\site-packages (from sqlalchemy) (2.0.1)\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "pip install sqlalchemy -i https://pypi.tuna.tsinghua.edu.cn/simplw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e930611e",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "e492e445",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>python</th>\n",
       "      <th>pandas</th>\n",
       "      <th>pytorch</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>95</td>\n",
       "      <td>111</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>56</td>\n",
       "      <td>18</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>113</td>\n",
       "      <td>23</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>49</td>\n",
       "      <td>116</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>118</td>\n",
       "      <td>81</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   python  pandas  pytorch\n",
       "0      95     111       20\n",
       "1      56      18      100\n",
       "2     113      23       17\n",
       "3      49     116       14\n",
       "4     118      81       16"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = numpy.random.randint(0,150,size=(150,3))\n",
    "df = pandas.DataFrame(data=data,columns=['python','pandas','pytorch'])\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "bae111af",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 连接数据库\n",
    "# root:root 是数据库用户名和密码\n",
    "# localhost:3306 是数据库地址和端口号\n",
    "# db 是数据库名字\n",
    "conn = create_engine('mysql+pymysql://root:111111@localhost:3306/db')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b073c3e",
   "metadata": {},
   "source": [
    "- to_sql() 保存到MySQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "37428174",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "150"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.to_sql(\n",
    "    name='score', # 数据库中表的名字\n",
    "    con=conn, # 数据库连接对象\n",
    "    index=False, # 是否保留行索引\n",
    "    if_exists='append', # append表示如果表存在，则追加数据\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88a679dc",
   "metadata": {},
   "source": [
    "- read_sql() 从MySQL中加载数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "66fad090",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>python</th>\n",
       "      <th>pandas</th>\n",
       "      <th>pytorch</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>95</td>\n",
       "      <td>111</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>56</td>\n",
       "      <td>18</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>113</td>\n",
       "      <td>23</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>49</td>\n",
       "      <td>116</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>118</td>\n",
       "      <td>81</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>34</td>\n",
       "      <td>94</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>16</td>\n",
       "      <td>12</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>47</td>\n",
       "      <td>140</td>\n",
       "      <td>107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>19</td>\n",
       "      <td>134</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>32</td>\n",
       "      <td>22</td>\n",
       "      <td>113</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>150 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     python  pandas  pytorch\n",
       "0        95     111       20\n",
       "1        56      18      100\n",
       "2       113      23       17\n",
       "3        49     116       14\n",
       "4       118      81       16\n",
       "..      ...     ...      ...\n",
       "145      34      94       63\n",
       "146      16      12       20\n",
       "147      47     140      107\n",
       "148      19     134       25\n",
       "149      32      22      113\n",
       "\n",
       "[150 rows x 3 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pandas.read_sql(\n",
    "    sql='select * from score', # sql语句\n",
    "    con=conn, # 连接的对象\n",
    "#     index_col='python', # 指定行索引\n",
    ")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
